package bean;

import java.awt.Graphics2D;
import java.awt.image.BufferedImage;
import java.awt.image.renderable.ParameterBlock;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Enumeration;

import javax.imageio.ImageIO;
import javax.media.jai.JAI;
import javax.media.jai.RenderableOp;
import javax.media.jai.RenderedOp;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class MboardDTO {
	private Connection conn;
	private MboardVO vo;
	
	
	private int listsize = 10; // 페이지당 표시되는 행 수 
	private int blocksize =3; // 한블럭당 표시되는 페이지 수 
	private int totsize=0; // 전체 게시물 수 
	private int totpage=0; //전페 페이지 수 
	private int totblock=0;//전체 블럭 수 
	private int nowpage =1; //현재 페이지
	private int nowblock=1;//현재 블럭
	private int startno = 0;//리스트 목록의 시작 위치
	private int endno=0; //리스트 목록의 끝 위치
	private int startpage=0; //현재 블럭에서 표시되는 시작 블럭 번호
	private int endpage =0; //현재 블럭에서 표시되는 끝 블럭 번호
	
	
	public MboardDTO(){
		conn = new MyOracle().getConn();
	}
	
	public ArrayList<MboardVO> select(){
		page();
		ArrayList<MboardVO> ar = new ArrayList<MboardVO>();
		try{
		String sql ="";
		PreparedStatement pst=null;
		ResultSet rs;
		if(vo.getSearch().equals("")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, startno);
			pst.setInt(2, endno);
		}else if(vo.getSearch().equals("mid")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where mid like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setInt(2, startno);
			pst.setInt(3, endno);
		}else if(vo.getSearch().equals("subject")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where subject like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setInt(2, startno);
			pst.setInt(3, endno);
		}else if(vo.getSearch().equals("wdate")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where wdate like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setInt(2, startno);
			pst.setInt(3, endno);
		}else if(vo.getSearch().equals("category")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where category like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setInt(2, startno);
			pst.setInt(3, endno);
		}else if(vo.getSearch().equals("content")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where content like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setInt(2, startno);
			pst.setInt(3, endno);
		}else if(vo.getSearch().equals("*")){
			sql ="select * from(select rownum rn, a.* from(select * from mboard where content like ? or category like ? or wdate like ? or subject like ? or mid like ? order by grp desc, lev)a)where rn between ? and ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%"+vo.getFind()+"%");
			pst.setString(2, "%"+vo.getFind()+"%");
			pst.setString(3, "%"+vo.getFind()+"%");
			pst.setString(4, "%"+vo.getFind()+"%");
			pst.setString(5, "%"+vo.getFind()+"%");
			pst.setInt(6, startno);
			pst.setInt(7, endno);
		}
			rs = pst.executeQuery();
		
			while(rs.next()){
				MboardVO v = new MboardVO();
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setContent(rs.getString("content"));
				v.setCategory(rs.getString("category"));
				v.setVno(rs.getInt("vno"));
				v.setRno(rs.getInt("rno"));
				v.setGrp(rs.getInt("grp"));
				v.setLev(rs.getString("lev"));
				v.setWdate(rs.getString("wdate"));
				v.setTag(rs.getString("tag"));
				v.setAttfile(rs.getString("attfile"));
				ar.add(v);
			}
			rs.close();
			pst.close();
			
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return ar;
	}
	
	
	public MboardVO selectone(){
		MboardVO v = new MboardVO();
		String sql="select * from mboard where rno = ?";
		
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, vo.getRno());
			
			ResultSet rs = pst.executeQuery();
			
			if(rs.next()){
				v.setMid(rs.getString("mid"));
				v.setSubject(rs.getString("subject"));
				v.setCategory(rs.getString("category"));
				v.setContent(rs.getString("content"));
				v.setTag(rs.getString("tag"));
				v.setAttfile(rs.getString("attfile"));
				v.setVno(rs.getInt("vno"));
				v.setRno(rs.getInt("rno"));
				v.setGrp(rs.getInt("grp"));
				v.setLev(rs.getString("lev"));
			}
			rs.close();
			pst.close();
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		return v;
	}
	
	public boolean input(){
		boolean b = false;
		String sql ="insert into mboard(mid,subject,content,rno,vno,grp,lev,wdate,tag,attfile,category)"
				+ " values(?,?,?,mboard_seq.nextval,?,mboard_seq.nextval,mboard_seq.nextval,sysdate,?,?,?)";
		try{
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, vo.getMid());
		pst.setString(2, vo.getSubject());
		pst.setString(3, vo.getContent());
		pst.setInt(4, 0);
		pst.setString(5, vo.getTag());
		pst.setString(6, vo.getAttfile());
		pst.setString(7, vo.getCategory());
		
		int n = pst.executeUpdate();
		
		if(n>0){
			b=true;
		}
		
		
		pst.close();
		
						
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return b;
	}
	public boolean reinput(String v){
		boolean b = false;
		String sql ="insert into mboard(mid,subject,content,rno,vno,grp,lev,wdate,tag,attfile,category)"
				+ " values(?,?,?,mboard_seq.nextval,?,?,?||'-'|| mboard_seq.nextval,sysdate,?,?,?)";
		try{
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, vo.getMid());
		pst.setString(2, vo.getSubject());
		pst.setString(3, vo.getContent());
		pst.setInt(4, vo.getVno());
		pst.setInt(5, vo.getGrp());
		pst.setString(6, v);
		pst.setString(7, vo.getTag());
		pst.setString(8, vo.getAttfile());
		pst.setString(9, vo.getCategory());
		
		int n = pst.executeUpdate();
		
		if(n>0){
			b=true;
		}
		pst.close();
		
						
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return b;
	}
	
	public boolean delete(){
		boolean b = false;
		String sql = "delete from mboard where rno = ?";
		try{
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setInt(1, vo.getRno());
		
		int a = pst.executeUpdate();
		
		if(a>0){
			if(vo.getAttfile()!=null){
				String dir = "F:/java work space/web_1406/WebContent/mboardfile/";
				File f = new File(dir+vo.getAttfile());
				if(f.exists()){
					f.delete();
				}				
					
			}
			b=true;
			
		}
		pst.close();
		
						
		}catch(Exception e){
			e.printStackTrace();
		}
		return b;
	}
	
	public boolean modify(int rno){
		boolean b = false;
		String sql = "update mboard set subject=?,content=?,category=?,tag=? where rno=?";
		try{
		PreparedStatement pst = conn.prepareStatement(sql);
		pst.setString(1, vo.getSubject());
		pst.setString(2, vo.getContent());
		pst.setString(3, vo.getCategory());
		pst.setString(4, vo.getTag());
		pst.setInt(5, rno);
		
		int n = pst.executeUpdate();
		
		if(n>0){
			b=true;
		}		
		
		}catch(Exception e){
			e.printStackTrace();
		}
		return b;
	}


	public MboardVO getVo() {
		return vo;
	}
	
	public void page(){ //페이징 관련 연산
		
		try{
			String sql="";
			PreparedStatement pst=null;
			
			if(vo.getSearch().equals("")){
				sql = "select count(*) cnt from mboard";
				pst = conn.prepareStatement(sql);
			}else if(vo.getSearch().equals("mid")){
				sql = "select count(*) cnt from mboard where mid like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
			}else if(vo.getSearch().equals("subject")){
				sql = "select count(*) cnt from mboard where subject like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
			}else if(vo.getSearch().equals("wdate")){
				sql = "select count(*) cnt from mboard where wdate like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
			}else if(vo.getSearch().equals("category")){
				sql = "select count(*) cnt from mboard where category like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
			}else if(vo.getSearch().equals("content")){
				sql = "select count(*) cnt from mboard where content like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
			}else if(vo.getSearch().equals("*")){
				sql ="select count(*) cnt from mboard where content like ? or category like ? or wdate like ? or subject like ? or mid like ?";
				pst = conn.prepareStatement(sql);
				pst.setString(1, "%"+vo.getFind()+"%");
				pst.setString(2, "%"+vo.getFind()+"%");
				pst.setString(3, "%"+vo.getFind()+"%");
				pst.setString(4, "%"+vo.getFind()+"%");
				pst.setString(5, "%"+vo.getFind()+"%");
			}
			
			
						
			ResultSet rs = pst.executeQuery();
			
			if(rs.next()){
			nowpage = vo.getNowpage();
			totsize = rs.getInt("cnt");
			totpage = (int)Math.ceil(totsize*1.0/listsize);
			totblock = (int)Math.ceil(totpage*1.0/blocksize);
			nowblock = (int)Math.ceil(nowpage*1.0/blocksize);
			endpage = nowblock*blocksize;
			startpage = endpage-blocksize+1;
			endno = nowpage*listsize;
			startno = endno-listsize+1;
			
			
				if(endpage>totpage){
					endpage = totpage;
				}
				if(endno>totsize){
					endno=totsize;
				}
			}
				
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
	}


	public void setVo(MboardVO vo) {
		if(vo.getRequest()!=null){
			try{
				
				int size = 10*1024*1024;
				String savedir = "F:/java work space/web_1406/WebContent/mboardfile/";
				MultipartRequest multi = new MultipartRequest(vo.getRequest(), savedir, size,"utf-8",new DefaultFileRenamePolicy());
				
				
				Enumeration file = multi.getFileNames();
				String tempfile = (String)file.nextElement();
				String orifile = multi.getOriginalFileName(tempfile);
				String sysfile = multi.getFilesystemName(tempfile);
				vo.setAttfile(sysfile);
				
				vo.setMid(multi.getParameter("mid"));
//				vo.setRno(Integer.parseInt(multi.getParameter("rno")));
				vo.setSubject(multi.getParameter("subject"));
				vo.setContent(multi.getParameter("content"));
				vo.setCategory(multi.getParameter("category"));
				vo.setLev(multi.getParameter("lev"));
				vo.setLev(multi.getParameter("lev"));
				if(multi.getParameter("grp")!=null){
				vo.setGrp(Integer.parseInt(multi.getParameter("grp")));
				}
				
				//섬네일 작업
				
//				ParameterBlock pd = new ParameterBlock();
//				pd.add(savedir + sysfile);
//				RenderedOp rop = JAI.create("fileload", pd);
//				
//				BufferedImage bi = rop.getAsBufferedImage();
//				BufferedImage thumb = new BufferedImage(100, 100, BufferedImage.TYPE_INT_RGB);
//				Graphics2D g = thumb.createGraphics();
//				g.drawImage(bi, 0, 0, 100, 100,null);
//				
//				File fi = new File(savedir+"sm_"+sysfile);
//				ImageIO.write(thumb, "png", fi);
				
			}catch(Exception e){
				e.printStackTrace();
			
			}
		}
		this.vo = vo;
		
	}
	public void viewcount(int v){
		String sql="update mboard set vno =? where rno =?";
		try{
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, v+1);
			pst.setInt(2, vo.getRno());
			
			int a = pst.executeUpdate();
			
			pst.close();
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
	}

	public int getListsize() {
		return listsize;
	}

	public void setListsize(int listsize) {
		this.listsize = listsize;
	}

	public int getBlocksize() {
		return blocksize;
	}

	public void setBlocksize(int blocksize) {
		this.blocksize = blocksize;
	}

	public int getTotsize() {
		return totsize;
	}

	public void setTotsize(int totsize) {
		this.totsize = totsize;
	}

	public int getTotpage() {
		return totpage;
	}

	public void setTotpage(int totpage) {
		this.totpage = totpage;
	}

	public int getTotblock() {
		return totblock;
	}

	public void setTotblock(int totblock) {
		this.totblock = totblock;
	}

	public int getNowpage() {
		return nowpage;
	}

	public void setNowpage(int nowpage) {
		this.nowpage = nowpage;
	}

	public int getNowblock() {
		return nowblock;
	}

	public void setNowblock(int nowblock) {
		this.nowblock = nowblock;
	}

	public int getStartno() {
		return startno;
	}

	public void setStartno(int startno) {
		this.startno = startno;
	}

	public int getEndno() {
		return endno;
	}

	public void setEndno(int endno) {
		this.endno = endno;
	}

	public int getStartpage() {
		return startpage;
	}

	public void setStartpage(int startpage) {
		this.startpage = startpage;
	}

	public int getEndpage() {
		return endpage;
	}

	public void setEndpage(int endpage) {
		this.endpage = endpage;
	}
	
	
	
	
	
	
}
